package de.rj.urwalking.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;

import android.hardware.SensorManager;
import android.util.Log;
import de.ralf.urwalking.soplugin.SOQueryBuilder;
import de.rj.urwalking.config.Config;
import de.rj.urwalking.data.LoginResult;
import de.rj.urwalking.data.Participant;
import de.rj.urwalking.data.Poi;
import de.rj.urwalking.data.Point;
import de.rj.urwalking.data.survey.Question;
import de.rj.urwalking.data.survey.Scale;
import de.rj.urwalking.exceptions.LoginException;
import de.rj.urwalking.util.Util;

/**
 * 
 * Manages the connection to our remote Postgresql-DB.
 * 
 * @author MKA
 * @author rjj
 */
public class ReadWritePostgreSQL {

	private static final String TAG = Config.TAG;
	private static final String DB_URL = Config.DB_URL;
	private static final String DB_USER = Config.DB_USER;
	private static final String DB_PASSWORD = Config.DB_PASSWORD;

	// status values from table "urw_status"
	// duplicated here for easier access, assuming they won't change very often
	private static final int STATUS_ACTIVE = 1;
	private static final int STATUS_INACTIVE = 2;
	private static final int STATUS_PENDING_REVIEW = 3;
	private static final int STATUS_FINISHED = 4;
	private static final int STATUS_CANCELLED = 5;

	private Connection connection = null;

	// @formatter:off
	private String pointsTemplate = 
			"SELECT gebid,x,y,hoehegeb,centroidx,centroidy,lagehnr,idxp " + 
			"FROM regensburg_points " +
			"WHERE ST_DWithin(geom, ST_GeomFromText('POINT(%f %f)'),%f);";

	private String poiTagTemplate = 
			"SELECT urw_osm_poi.ref_osm_id, x, y, tag, value " + 
			"FROM urw_osm_poi, urw_osm_tag_tags, urw_osm_tag_values, urw_osm_poi_tag " +
			"WHERE urw_osm_poi.ref_osm_id IN (" +
				"SELECT ref_osm_id " +
				"FROM urw_osm_poi " +
				"WHERE ST_DWithin(geom_point, ST_GeomFromText('POINT(%f %f)', 31468), %f)) " +
			"AND urw_osm_tag_tags.tagid = urw_osm_poi_tag.reftagid " +
			"AND urw_osm_tag_values.valueid = urw_osm_poi_tag.refvalueid " +
			"AND urw_osm_poi.ref_osm_id = urw_osm_poi_tag.refosmid;";
	
	private String enhancedScalesQuery = "SELECT urw_scale.uniqueid, urw_scale.name, urw_scale.extremityonly, urw_scale.ispicturebased, sm.intvalue, sm.name as scalepoint, sm.refpicture as pictureid, sm.isindoor, sm.isoutdoor, sm.ordinalnumber " +
			"FROM urw_scale LEFT OUTER JOIN urw_scale_mapping sm ON urw_scale.uniqueid = sm.refscale " +
			"WHERE urw_scale.uniqueid IN (" +
			    "SELECT refscale " +
			    "FROM urw_questions " +
				"WHERE urw_questions.uniqueid IN (%s) " +
			") " +  
			"ORDER BY uniqueid, ordinalnumber;";
	
	private String questionsQueryById = "SELECT urw_questions.uniqueid AS id, text, ordinalnumber, refscale, screen " +
			"FROM urw_questions " +
			"WHERE uniqueid IN (%s) "+
			"ORDER BY screen, ordinalnumber;";
	
	private String questionsIdQuery = "SELECT urw_questions.uniqueid AS uniqueid " +
			"FROM urw_questions INNER JOIN urw_survey_section s ON urw_questions.refsectionnumber = s.uniqueid " + 
			"WHERE s.refsurvey = ? " +
			"ORDER BY s.ordinalnumber, urw_questions.ordinalnumber;";
	
	private String experimentsQuery = "SELECT urw_experiment.uniqueid, refroute, description, title, blnoutdoor, q.refquestionid as questionid, issmartphonebased, istabletbased "
			+ "FROM urw_experiment LEFT OUTER JOIN urw_experiment_questions q ON urw_experiment.uniqueid = q.refexperimentid "
			+ "WHERE refstatus = 1 AND (issmartphonebased OR istabletbased);";

	private String checkEmailQuery = "SELECT uniqueid " + "FROM urw_participants "
			+ "WHERE email = ?";

	private String checkPasswordQuery = "SELECT uniqueid " + "FROM urw_participants "
			+ "WHERE email = ? AND password = ? ";

	private String updateLastLoginQuery = "UPDATE urw_participants "
			+ "SET lastlogin = ? " + "WHERE uniqueid = ?";

	private String registerParticipantQuery = "INSERT INTO urw_participants (uniqueid,email,password,lastlogin,createdon,refstatus, hasacceptedconditions) "
			+ "VALUES (nextval(pg_get_serial_sequence('urw_participants','uniqueid')), ? , ? , ? , ? ,1, true) "
			+ "RETURNING uniqueid";

	private String checkParticipantQuery = "SELECT uniqueid	FROM urw_participants WHERE	email = ?";
		
	private String fetchIndoorSalientObjectsQuery = 
			"SELECT urw_urw_salient_object.uniqueid as id, name, \"centroidX\", \"centroidY\", refindoor " +
			"FROM urw_urw_salient_object INNER JOIN urw_indoor_object io ON urw_urw_salient_object.refindoor = io.uniqueid";
	
	private String checkUrwVersionQuery = "SELECT refstatus FROM urw_versions WHERE urwalkingversion = ?";
	
	// @formatter:on

	public ReadWritePostgreSQL() {

		// load postgresql driver
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			Log.e(TAG, "Could not find postgreSQL driver!");
		}

		// connect to DB
		try {
			connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			Log.i(TAG, ReadWritePostgreSQL.class.getName() + " -> Connected to DB successfully.");
		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}
	}

	public boolean isConnected() {
		return connection != null;
	}

	/**
	 * Please note: column geom in DB contains a representation of the centroid!
	 * 
	 * @param easting
	 * @param northing
	 * @param radius
	 * @return an ArrayList of Point objects, each object representing a row in
	 *         regensburg_points table; centroid must be located within radius
	 */
	public ArrayList<Point> getPointsWithinRadius(double easting, double northing, double radius) {

		ArrayList<Point> points = new ArrayList<Point>();

		// return an empty ArrayList if there's no connection
		if (!isConnected()) {
			return points;
		}

		ResultSet resultSet = null;
		String sql;

		try {

			sql = String.format(Locale.US, pointsTemplate, easting, northing, radius);
			Log.i(TAG, "SQL-Statement issued was: " + sql);
			resultSet = connection.createStatement().executeQuery(sql);

			while (resultSet.next()) {
				points.add(new Point(resultSet.getInt("gebid"), resultSet.getString("lagehnr"),
						resultSet.getInt("idxp"), resultSet.getDouble("centroidx"), resultSet
								.getDouble("centroidy"), resultSet.getDouble("x"), resultSet
								.getDouble("y"), resultSet.getDouble("hoehegeb")));
			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}

		Log.i(TAG, "got " + points.size() + " points from DB");
		return points;
	}

	/**
	 * 
	 * Fetches all POIs within a certain radius + their tag/value pairs
	 * 
	 * @param easting
	 * @param northing
	 * @param radius
	 * @return an ArrayList of Poi objects
	 */

	public ArrayList<Poi> getPOIsWithinRadius(double easting, double northing, double radius) {
		HashMap<Integer, Poi> pois = new HashMap<Integer, Poi>();

		// return an empty ArrayList if there's no connection
		if (!isConnected()) {
			return new ArrayList<Poi>(pois.values());
		}

		ResultSet resultSet = null;
		String sql;

		try {

			sql = String.format(Locale.US, poiTagTemplate, easting, northing, radius);
			Log.i(TAG, "SQL-Statement issued was: " + sql);
			resultSet = connection.createStatement().executeQuery(sql);

			while (resultSet.next()) {

				int ref_osm_id = resultSet.getInt("ref_osm_id");

				// check if we already created this poi
				if (!pois.containsKey(ref_osm_id)) {
					Poi poi = new Poi(ref_osm_id, resultSet.getDouble("x"),
							resultSet.getDouble("y"));
					pois.put(ref_osm_id, poi);
				}

				// add tags to poi
				Poi poi = pois.get(ref_osm_id);
				poi.addTag(resultSet.getString("tag"), resultSet.getString("value"));

			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}
		Log.i(TAG, "got " + pois.values().size() + " pois from DB");

		return new ArrayList<Poi>(pois.values());
	}

	public HashMap<Integer, Scale> fetchScalesFor(ArrayList<Integer> questionIds) {

		HashMap<Integer, Scale> scales = new HashMap<Integer, Scale>();
		ResultSet resultSet = null;

		try {

			String query = String.format(enhancedScalesQuery, Util.join(", ", questionIds));

			Log.i(TAG, "SQL-Statement issued was: " + query);
			resultSet = connection.createStatement().executeQuery(query);

			while (resultSet.next()) {

				int scaleId = resultSet.getInt("uniqueid");
				String scaleName = resultSet.getString("name");
				boolean extremityOnly = resultSet.getBoolean("extremityonly");
				boolean isPictureBased = resultSet.getBoolean("ispicturebased");
				int intValue = resultSet.getInt("intvalue");
				String scalePoint = resultSet.getString("scalepoint");
				int pictureId = resultSet.getInt("pictureid");
				boolean isIndoor = resultSet.getBoolean("isindoor");
				boolean isOutdoor = resultSet.getBoolean("isoutdoor");

				if (scales.get(scaleId) == null) {
					scales.put(scaleId,
							new Scale(scaleId, scaleName, extremityOnly, isPictureBased));
				}
				scales.get(scaleId).addScalePoint(scalePoint, intValue, pictureId, isIndoor,
						isOutdoor);

			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
			return null;
		}
		Log.i(TAG, "got " + scales.size() + " scales from DB");
		return scales;

	}

	/**
	 * Fetches all question ids for a specific survey, but does not build the
	 * actual questions.
	 * 
	 * @param surveyId
	 * @return an {@link ArrayList} of question ids
	 */

	public ArrayList<Integer> fetchQuestionIdsFor(int surveyId) {
		ArrayList<Integer> questionIds = new ArrayList<Integer>();
		ResultSet resultSet = null;

		try {

			PreparedStatement statement = connection.prepareStatement(questionsIdQuery,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setInt(1, surveyId);
			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				questionIds.add(resultSet.getInt("uniqueid"));
			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}
		Log.i(TAG, "got " + questionIds.size() + " question ids from DB");
		return questionIds;
	}

	/**
	 * Fetches and builds the questions with the specified ids from the database
	 * 
	 * @param questionIds
	 * @return
	 */

	public HashMap<Integer, Question> fetchQuestions(ArrayList<Integer> questionIds) {
		HashMap<Integer, Question> questions = new HashMap<Integer, Question>();
		ResultSet resultSet = null;

		try {

			String query = String.format(questionsQueryById, Util.join(", ", questionIds));

			Log.i(TAG, "SQL-Statement issued was: " + query);
			resultSet = connection.createStatement().executeQuery(query);

			while (resultSet.next()) {

				Question q = Question.createFromDB(resultSet);
				questions.put(resultSet.getInt("id"), q);

			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
			return null;
		}
		Log.i(TAG, "got " + questions.size() + " questions from DB");
		return questions;
	}

	/**
	 * 
	 * Fetches all experiments and the questions they contain.
	 * 
	 * @return an ArrayList of {@link DBExperiment} objects
	 */

	public ArrayList<DBExperiment> fetchExperiments() {

		HashMap<Integer, DBExperiment> experiments = new HashMap<Integer, DBExperiment>();
		ResultSet resultSet = null;

		try {

			PreparedStatement statement = connection.prepareStatement(experimentsQuery,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			resultSet = statement.executeQuery();

			while (resultSet.next()) {

				int experimentId = resultSet.getInt("uniqueid");

				if (experiments.get(experimentId) == null) {

					DBExperiment e = new DBExperiment(resultSet.getInt("uniqueid"),
							resultSet.getInt("refroute"), resultSet.getString("title"),
							resultSet.getString("description"), resultSet.getBoolean("blnoutdoor"));

					experiments.put(experimentId, e);
				}
				experiments.get(experimentId).addQuestionId(resultSet.getInt("questionId"));

			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}
		Log.i(TAG, "got " + experiments.size() + " experiments from DB");
		return new ArrayList<DBExperiment>(experiments.values());
	}

	/**
	 * Checks if a user with the supplied email address and password exists in
	 * the database.
	 * 
	 * @param email
	 * @param password
	 * @return a LoginResult object which may contain possible error states or a
	 *         User object (if login was successful)
	 * @throws LoginException
	 */

	public LoginResult checkCredentials(String email, String password) throws LoginException {

		ResultSet resultSet = null;

		try {
			PreparedStatement statement = connection.prepareStatement(checkEmailQuery,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setString(1, email.toLowerCase(Locale.getDefault()));
			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			resultSet = statement.executeQuery();

			int rowCount = resultSet.last() ? resultSet.getRow() : 0;
			if (rowCount == 1) {
				// email address found in database, now check password
				PreparedStatement passwordStatement = connection.prepareStatement(
						checkPasswordQuery, ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				passwordStatement.setString(1, email.toLowerCase(Locale.getDefault()));
				passwordStatement.setString(2, Util.md5(password));

				Log.i(TAG, "SQL-Statement issued was: " + passwordStatement.toString());
				resultSet = passwordStatement.executeQuery();

				rowCount = resultSet.last() ? resultSet.getRow() : 0;
				if (rowCount == 1) {
					// password ok, user logged in successfully
					int participantId = resultSet.getInt("uniqueid");
					updateLastLogin(participantId);
					return new Participant(participantId, email);
				} else {
					return new LoginResult(true, false, email);
				}
			} else {
				return new LoginResult(false, false, email);
			}
		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
			throw new LoginException();

		}

	}

	/**
	 * Updates the timestamp "lastlogin" in table urw_participants for a
	 * specific participant to the current time.
	 * 
	 * @param userId
	 * @throws SQLException
	 */

	private void updateLastLogin(int userId) throws SQLException {

		PreparedStatement statement = connection.prepareStatement(updateLastLoginQuery,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		statement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
		statement.setInt(2, userId);
		Log.i(TAG, "SQL-Statement issued was: " + statement.toString());
		statement.executeUpdate();
	}

	/**
	 * Registers a paricipant by inserting a new row into table urw_paricipants.
	 * Both timestamps are set to the current time.
	 * 
	 * @param email
	 * @param password
	 * @return true if registration was successful, false if there was an
	 *         SQLException, e.g. if there already exists a user with this email
	 *         address.
	 * 
	 */

	public int registerParticipant(String email, String password) {

		try {
			PreparedStatement statement = connection.prepareStatement(registerParticipantQuery,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setString(1, email.toLowerCase(Locale.getDefault()));
			statement.setString(2, Util.md5(password));

			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			statement.setTimestamp(3, timestamp);
			statement.setTimestamp(4, timestamp);

			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getInt("uniqueid");
			} else {
				return -1;
			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
			return -1;
		}
	}

	/**
	 * Checks if there is already a participant with the given email
	 * 
	 * @param email
	 * @return the participant's uniqueid, or -1 if no match was found
	 */

	public int checkParticipant(String email) {

		try {
			PreparedStatement checkParticipantStatement = connection.prepareStatement(
					checkParticipantQuery, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			checkParticipantStatement.setString(1, email);

			Log.i(TAG, "SQL-Statement issued was: " + checkParticipantStatement.toString());

			ResultSet resultSet = checkParticipantStatement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getInt("uniqueid");
			} else {
				return -1;
			}
		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
			return -1;
		}
	}

	/**
	 * 
	 * @param epsg31468coords
	 *            The location of the device, already converted to EPSG31468
	 *            coordinates.
	 * @param orientation
	 *            The orientation of the device as we got it from
	 *            {@link SensorManager#getOrientation(float[], float[])}
	 * @return An ArrayList of {@link DBSalientObject} objects, each one
	 *         representing a single line in urw_urw_salient_object
	 */
	public ArrayList<DBSalientObject> fetchSalientObjects(double[] epsg31468coords,
			float orientation) {

		// Create an ArrayList that will hold the objects
		ArrayList<DBSalientObject> salientObjects = new ArrayList<DBSalientObject>();
		if (!isConnected()) {
			return salientObjects;
		}

		// Build the query using Florian's and Andreas' code
		SOQueryBuilder soQueryBuilder = new SOQueryBuilder(orientation, epsg31468coords[1],
				epsg31468coords[0]);
		String query = soQueryBuilder.buildQuery();

		// Put everything in a try-catch-block to catch possible DB errors
		try {
			// Create the prepared statement with our query
			PreparedStatement statement = connection.prepareStatement(query,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			// Actually execute the query and get the results
			ResultSet resultSet = statement.executeQuery();

			// Iterate through the results
			while (resultSet.next()) {
				// Create an object for every line
				DBSalientObject s = new DBSalientObject(resultSet.getInt("uniqueid"),
						resultSet.getInt("refpoi"), resultSet.getInt("refgebid"),
						resultSet.getInt("refindoor"), resultSet.getDouble("x"),
						resultSet.getDouble("y"), resultSet.getString("name"));

				// Add the object to the previously created ArrayList
				salientObjects.add(s);
			}

		} catch (SQLException sqlEx) {
			// If we get here, something went wrong
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			// Let's at least try to close the connection
			closeConnection();
		}
		Log.i(TAG, "got " + salientObjects.size() + " salient objects from DB");
		// Finally, return the loaded objects
		return salientObjects;
	}

	public ArrayList<DBSalientObject> fetchIndoorSalientObjects() {

		ArrayList<DBSalientObject> salientObjects = new ArrayList<DBSalientObject>();

		if (!isConnected()) {
			return salientObjects;
		}

		ResultSet resultSet = null;

		try {

			PreparedStatement statement = connection.prepareStatement(
					fetchIndoorSalientObjectsQuery, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());

			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				DBSalientObject s = new DBSalientObject(resultSet.getInt("id"), null, null,
						resultSet.getInt("refindoor"), resultSet.getDouble("centroidX"),
						resultSet.getDouble("centroidY"), resultSet.getString("name"));

				salientObjects.add(s);
			}

		} catch (SQLException sqlEx) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + sqlEx.getMessage(), sqlEx);
			closeConnection();
		}
		Log.i(TAG, "got " + salientObjects.size() + " indoor salient objects from DB");
		return salientObjects;

	}

	/**
	 * Checks if the currently used version is marked as active in the database.
	 * 
	 * @param urwVersion
	 * @return true if active, false if any other status or not in db
	 */

	public boolean checkUrwVersion(String urwVersion) {

		if (!isConnected())
			return false;

		try {
			PreparedStatement statement = connection.prepareStatement(checkUrwVersionQuery,
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			statement.setString(1, urwVersion);
			Log.i(TAG, "SQL-Statement issued was: " + statement.toString());
			ResultSet resultSet = statement.executeQuery();

			// check if this resultset has any lines, i.e.
			if (resultSet.first()) {

				int status = resultSet.getInt("refstatus");
				if (status == STATUS_ACTIVE) {
					// urw version marked as active, everything's fine
					return true;
				} else {
					// urw version marked as something else (probably inactive)
					return false;
				}

			} else {
				// no database entry for this urw version
				return false;
			}

		} catch (SQLException e) {
			Log.e(TAG, ReadWritePostgreSQL.class.getName() + " " + e.getMessage(), e);
			e.printStackTrace();
			return false;
		}

	}

	/**
	 * Tries to close the connection, if there is one.
	 */

	public void closeConnection() {
		if (connection != null) {
			try {
				connection.close();
				Log.i(TAG, ReadWritePostgreSQL.class.getName()
						+ " -> Successfully closed connection.");
			} catch (SQLException exCon) {
				Log.w(TAG,
						ReadWritePostgreSQL.class.getName() + " Wasn't able to close connection",
						exCon);
			}
		}
	}
}
